﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IsmsExplorer.Model.RiskRegister;
using System.Diagnostics;
using IsmsExplorer.Infrastructure.Helper;
using IsmsExplorer.Infrastructure.RepositoryFramework;
using IsmsExplorer.Model.Documents;
using IsmsExplorer.Model.Iso27001;

namespace IsmsExplorer.Infrastructure.Repositories.RiskRegister {
    internal class TreatmentRepository : SqlServerRepositoryBase<Treatment>, ITreatmentRepository {
        #region Data
        #endregion

        #region Constructors
        public TreatmentRepository(IUnitOfWork unitOfWork) : base(unitOfWork) {
        }

        public TreatmentRepository() : this(null) {
        }
        #endregion

        #region ITreatmentRepository members

        public Treatment FindBy(Threat threat) {
            if (threat != null) {
                Debug.Assert(threat != null);
                Debug.Assert(threat.ID > 0);
                StringBuilder sb = new StringBuilder(GetBaseQuery());
                return this.BuildEntityFromSql(sb.AppendFormat(" WHERE {0}={1}",
                    TreatmentFactory.FieldNames.ThreatID, DataHelper.GetSqlValue(threat.ID))
                    .Append(";").ToString());
            } else {
                return null;
            }
        }

        public void AssociateIsoControls(Treatment treatment, IList<Iso27001Control> controls) {            
            if (treatment != null && controls != null && controls.Count > 0) {
                StringBuilder sb = new StringBuilder("");
                sb.AppendFormat("INSERT INTO TreatmentApplications({0},{1},{2}) VALUES ({3},{4}",
                    TreatmentApplicationFactory.FieldNames.ProjectID,
                    TreatmentApplicationFactory.FieldNames.TreatmentID,
                    TreatmentApplicationFactory.FieldNames.ControlID,
                    treatment.ProjectID,
                    treatment.ID);
                sb.Append(",{0})");                
                foreach (Iso27001Control control in controls) {
                    string sql = string.Format(sb.ToString(), control.ID);
                    this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sql));
                }
            }
        }

        public void DisassociateIsoControls(Treatment treatment, IList<Iso27001Control> controls) {
            StringBuilder sb = new StringBuilder("DELETE FROM TreatmentApplications");
            sb.AppendFormat(" WHERE ({0}={1}) AND ({2}={3})",
                            TreatmentApplicationFactory.FieldNames.ProjectID, treatment.ProjectID,
                            TreatmentApplicationFactory.FieldNames.TreatmentID, treatment.ID);

            if (controls != null && controls.Count > 0) {
                sb.AppendFormat(" AND ({0} IN ({1}))",
                    TreatmentApplicationFactory.FieldNames.ControlID,
                    controls.Select(x => x.ID.ToString()).Aggregate((x, y) => x + "," + y));
            }
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sb.ToString()));
        }

        #endregion

        #region BuildChildDataCallbacks
        protected override void InitialiseChildCallbacks() {            
        }
        #endregion

        #region Child object callbacks
        #endregion

        #region GetBaseQuery
        protected override string GetBaseQuery() {
            return string.Format(@"SELECT {0},{1},{2},{3},{4},{5},{6} FROM [Treatments]",
                TreatmentFactory.FieldNames.ID,
                TreatmentFactory.FieldNames.ProjectID,
                TreatmentFactory.FieldNames.ThreatID,
                TreatmentFactory.FieldNames.Reference,
                TreatmentFactory.FieldNames.V,
                TreatmentFactory.FieldNames.L,
                TreatmentFactory.FieldNames.I);
        }
        #endregion

        #region GetBaseWhereClause
        protected override string GetBaseWhereClause() {
            StringBuilder sb = new StringBuilder(" WHERE ").Append(TreatmentFactory.FieldNames.ID).Append("={0}");
            return sb.ToString();
        }
        #endregion

        #region BuildEntityFromSql override (to get the Iso27001ControlDocumentation objects associated with Treatment).

        protected override Treatment BuildEntityFromSql(string sql) {
            Treatment treatment = base.BuildEntityFromSql(sql);
            if (treatment != null) {
                // Get the associated documents.
                IIso27001ControlDocumentationRepository r = RepositoryFactory.GetRepository<IIso27001ControlDocumentationRepository, Iso27001ControlDocumentation>();
                IList<Iso27001ControlDocumentation> docs = r.FindByTreatment(treatment.ProjectID, treatment.ID);
                foreach (var doc in docs) {
                    Debug.Assert(doc.IsoControl != null && doc.IsoControl.ID > 0);
                    treatment[doc.IsoControl.ID] = doc;
                }
            }
            return treatment;
        }
        #endregion

        #region UnitOfWork implementation        

        protected override void PersistDeletedItem(Treatment item) {
            // Disassociate all controls applied with this Treatment.            
            DisassociateIsoControls(item, null);
            string sql = string.Format(@"DELETE FROM [Treatments] {0}", this.BuildBaseWhereClause(item.ID));
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sql));
        }

        protected override void PersistNewItem(Treatment item) {
            Debug.Assert(item.Threat != null);
            Debug.Assert(item.Threat.ID > 0);
            StringBuilder sb = new StringBuilder(256);
            sb.AppendFormat(@"INSERT INTO [Treatments]({0},{1},{2},{3},{4},{5}) VALUES ({6},{7},{8},{9},{10},{11}); SELECT SCOPE_IDENTITY();",                
                TreatmentFactory.FieldNames.ProjectID,
                TreatmentFactory.FieldNames.ThreatID,
                TreatmentFactory.FieldNames.Reference,
                TreatmentFactory.FieldNames.V,
                TreatmentFactory.FieldNames.L,
                TreatmentFactory.FieldNames.I,
                DataHelper.GetSqlValue(item.ProjectID),
                DataHelper.GetSqlValue(item.Threat.ID),
                DataHelper.GetSqlValue(item.Reference),
                DataHelper.GetSqlValue(item.V),
                DataHelper.GetSqlValue(item.L),
                DataHelper.GetSqlValue(item.I)
                );
            item.ID = Convert.ToInt32(this.Database.ExecuteScalar(this.Database.GetSqlStringCommand(sb.ToString())));
            AssociateIsoControls(item, item.IsoControlList);
        }

        protected override void PersistUpdatedItem(Treatment item) {
            StringBuilder sb = new StringBuilder(@"UPDATE [Treatments] SET ");
            sb.AppendFormat("{0}={1},", TreatmentFactory.FieldNames.ProjectID, DataHelper.GetSqlValue(item.ProjectID));
            sb.AppendFormat("{0}={1},", TreatmentFactory.FieldNames.ThreatID, DataHelper.GetSqlValue(item.Threat.ID));
            sb.AppendFormat("{0}={1},", TreatmentFactory.FieldNames.Reference, DataHelper.GetSqlValue(item.Reference));
            sb.AppendFormat("{0}={1},", TreatmentFactory.FieldNames.V, DataHelper.GetSqlValue(item.V));
            sb.AppendFormat("{0}={1},", TreatmentFactory.FieldNames.L, DataHelper.GetSqlValue(item.L));
            sb.AppendFormat("{0}={1},", TreatmentFactory.FieldNames.I, DataHelper.GetSqlValue(item.I));
            // Remove the last unnecessary comma character!
            sb.Length -= ",".Length;
            sb.Append(" ").Append(this.BuildBaseWhereClause(item.ID));
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sb.ToString()));
            // Take care of potentially updated controls associated with this Treatment.
            // Remove all controls first and then associate the ones in the list.
            DisassociateIsoControls(item, null);
            AssociateIsoControls(item, item.IsoControlList);
        }
        #endregion

        #region Private helper/callback methods
        #endregion

    } // end internal class TreatmentRepository : SqlServerRepositoryBase<Asset>, ITreatmentRepository

}
